package org.compiere.process;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import javax.swing.JFileChooser;
import javax.swing.JList;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.process.FI_DeclarationOfVAT30007.line;
import org.compiere.sqlj.BPartner;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;
import org.utils.DataUtils;
import org.utils.TextParser;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class FI_DeclarationOfVAT300 extends SvrProcess {
   
	class line{
		public String getRow() {
			return row;
		}
		public void setRow(String row) {
			this.row = row;
		}
		public String getC_bpartner_id() {
			return c_bpartner_id;
		}
		public void setC_bpartner_id(String c_bpartner_id) {
			this.c_bpartner_id = c_bpartner_id;
		}
		public String getFi_tin() {
			return fi_tin;
		}
		public void setFi_tin(String fi_tin) {
			this.fi_tin = fi_tin;
		}
		public String getFi_bin_iin() {
			return fi_bin_iin;
		}
		public void setFi_bin_iin(String fi_bin_iin) {
			this.fi_bin_iin = fi_bin_iin;
		}
		public String getDocumentno() {
			return documentno;
		}
		public void setDocumentno(String documentno) {
			this.documentno = documentno;
		}
		public String getDateinvoiced() {
			return dateinvoiced;
		}
		public void setDateinvoiced(String dateinvoiced) {
			this.dateinvoiced = dateinvoiced;
		}
		public String getDateacct() {
			return dateacct;
		}
		public void setDateacct(String dateacct) {
			this.dateacct = dateacct;
		}
		public String getName() {
			return name;
		}

		public BigDecimal getTaxbaseamt() {
			return taxbaseamt;
		}
		public void setTaxbaseamt(BigDecimal taxbaseamt) {
			this.taxbaseamt = taxbaseamt;
		}
		public BigDecimal getTaxamt() {
			return taxamt;
		}
		public void setTaxamt(BigDecimal taxamt) {
			this.taxamt = taxamt;
		}
		public BigDecimal getTaxamt2() {
			return taxamt2;
		}
		public void setTaxamt2(BigDecimal taxamt2) {
			this.taxamt2 = taxamt2;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getCaption() {
			return caption;
		}
		public void setCaption(String caption) {
			this.caption = caption;
		}
		public String getPeriod() {
			return period;
		}
		public void setPeriod(String period) {
			this.period = period;
		}
		String row = "";
		String c_bpartner_id = "";
		String fi_tin = "";
		String fi_bin_iin = "";
		String documentno = "";
		String dateinvoiced = "";
		String dateacct = "";
		String name = "";
		BigDecimal taxbaseamt = new BigDecimal(0);
		BigDecimal taxamt = new BigDecimal(0);
		BigDecimal taxamt2 = new BigDecimal(0);
		String caption = "";
		String period = "";
		
	}
	
   /** Current context    */
   private Properties m_ctx;
   /**     */
   private ProcessInfo pi;
   /** Account Sign                    */
   private String                AccountSign = "N";
   /** */
   private Timestamp startDate = null;
   private Timestamp startDate2 = null; 
   private Timestamp endDate = null; 
   private Timestamp endDate2 = null;    
   boolean xml = false;
   
   private Timestamp            DateAcct_From = null;
   private Timestamp            DateAcct_To = null;
   /**    Client Parameter                    */
   private int                    AD_Client_ID = 0;
   /**    Org Parameter                    */
   private int                    AD_Org_ID = 0;
   /**    Account Parameter                */
   private int                    Account_ID = 0;
   /**    BPartner Parameter                */
   private int                    C_BPartner_ID = 0;
   /** User Element 1 Parameter        */
   private int                    UserElement1_ID = 0;
   /**    Parameter Where Clause            */
   //private StringBuffer        m_parameterWhere = new StringBuffer();
   /** */
   private static CLogger    s_log    = CLogger.getCLogger (BalanceSheets.class);
   /** */
   private String path = "";
   
   MBPartner bp_org = null;

   /**
    *  Prepare - e.g., get Parameters.
    */
   @Override
   protected void prepare() {
       log.info("");
       m_ctx = Env.getCtx();
       pi = getProcessInfo();
       ProcessInfoParameter[] para = getParameter();
       for (int i = 0; i < para.length; i++)
       {
           String name = para[i].getParameterName();
           if (para[i].getParameter() == null);
           else if (name.equals("startDate"))
               startDate = (Timestamp) para[i].getParameter();
           else if (name.equals("endDate"))
               endDate = (Timestamp) para[i].getParameter();
           else if (name.equals("startDate2"))
               startDate2 = (Timestamp) para[i].getParameter();
           else if (name.equals("endDate2"))
               endDate2 = (Timestamp) para[i].getParameter();
           else if(name.equals("xml")){
        	   String b = (String) para[i].getParameter();
        	   if(b.equals("Y"))
        		   xml = true;
           }
           else
               log.log(Level.SEVERE, "Unknown Parameter: " + name);
       }
       path = Util.localFilePath;
       /// here another code)
   }
   
   @Override
   protected String doIt() throws Exception {
	   
	   //get BP of org-------------------
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       
       String sql = "select c_bpartner_id from adempiere.c_bpartner where ad_orgbp_id = ?";	   
       try
       {
           pstmt = DB.prepareStatement(sql,null);
           pstmt.setInt(1, Env.getAD_Org_ID(Env.getCtx()));
           rs = pstmt.executeQuery();    
           while (rs.next ())
           {
        	   bp_org = new MBPartner(Env.getCtx(),rs.getInt(1), null);
           }
       }
       catch (SQLException e){
           s_log.log(Level.SEVERE, "Form 300", e);
       }
       finally{
           DB.close(rs, pstmt);
           rs = null; pstmt = null;
       }    
       //---------------------------------
       
       
       if(!xml)
    	   return toExcel();
       else 
    	   return toXML();
   }
   
   
   private String toXML() throws Exception {

	   final JFileChooser fc = new JFileChooser();
//		JList list = new JList();
		fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
   
	if (JFileChooser.APPROVE_OPTION == fc.showOpenDialog(new JList())) {
		String FileName = fc.getSelectedFile().getAbsolutePath() + "/FI_DeclarationOfVAT300.xml";

		File file = new File(FileName);
		FileOutputStream fos = null;
		OutputStreamWriter out = null;
		fos = new FileOutputStream(file);
		out = new OutputStreamWriter(fos, "UTF8");//"CP866");
		List<line> list30007 = new ArrayList<line>();
		List<line> list30008 = new ArrayList<line>();

		// 30007
		BigDecimal baseamt7 = null;
		BigDecimal baseamt7n = null;
		BigDecimal tax7 = null;
		
		baseamt7 = new BigDecimal(0);
		baseamt7n = new BigDecimal(0);
		tax7 = new BigDecimal(0);

		list30007 = getInvoices30007();		
		for(line l: list30007){
			if(l.getTaxamt().compareTo(new BigDecimal(0)) == 1){
				baseamt7 = baseamt7.add(l.taxbaseamt);
				tax7 = tax7.add(l.taxamt);
			}else
			baseamt7n = baseamt7n.add(l.taxbaseamt);
		}

		// 30008
		BigDecimal baseamt8 = null;
		BigDecimal baseamt8n = null;
		BigDecimal tax8 = null;

		
		baseamt8 = new BigDecimal(0);
		tax8 = new BigDecimal(0);
		baseamt8n = new BigDecimal(0);

		list30008 = getInvoices30008();		
		for(line l: list30008){
			if(l.getTaxamt().compareTo(new BigDecimal(0)) == 1){
				baseamt8 = baseamt8.add(l.taxbaseamt);
				tax8 = tax8.add(l.taxamt);
			}else
			baseamt8n = baseamt8n.add(l.taxbaseamt);		
		}		
		
		out.write(
		"<?xml version="+'"'+"1.0" +'"'+ " encoding="+'"'+"UTF-8"+'"'+"?>\n"+
		"<fno code="+'"'+"300.00"+'"'+" version="+'"'+"16"+'"'+" formatVersion="+'"'+"2"+'"'+">\n"+
		"	<form name=\"form_300_00\">\n" +
		"		<field name=\"accept_date\"/>\n" +
		"		<field name=\"category_w\"/>\n" +
		"		<field name=\"currency_code\">KZT</field>\n" +
		"		<field name=\"date_\"/>\n" +
		"		<field name=\"ded_meth1\">true</field>\n" +
		"		<field name=\"ded_meth2\"/>\n" +
		"		<field name=\"ded_meth3\"/>\n" +
		"		<field name=\"dt_additional\"/>\n" +
		"		<field name=\"dt_final\"/>\n" +
		"		<field name=\"dt_main\"/>\n" +
		"		<field name=\"dt_notice\"/>\n" +
		"		<field name=\"dt_regular\">true</field>\n" +
		"		<field name=\"uchr_dov\"/>\n" +
		"		<field name=\"upr_dov\"/>\n" +
		"		<field name=\"dt_w\"/>\n" +
		"		<field name=\"dt_w_2\"/>\n" +
		"		<field name=\"field_300_00_001_A\">"+baseamt7+"</field>\n" +
		"		<field name=\"field_300_00_001_B\">"+tax7+"</field>\n" +
		"		<field name=\"field_300_00_002\"/>\n" +
		"		<field name=\"field_300_00_003_A\"/>\n" +
		"		<field name=\"field_300_00_003_B\"/>\n" +
		"		<field name=\"field_300_00_004_A\"/>\n" +
		"		<field name=\"field_300_00_004_B\"/>\n" +
		"		<field name=\"field_300_00_005\"/>\n" +
		"		<field name=\"field_300_00_006\"/>\n" +
		"		<field name=\"field_300_00_007\">"+baseamt7+"</field>\n" +
		"		<field name=\"field_300_00_008\">\n" +
		"		<field name=\"field_300_00_009\"/>\n" +
		"		<field name=\"field_300_00_010\"/>\n" +
		"		<field name=\"field_300_00_011\"/>\n" +
		"		<field name=\"field_300_00_012\"/>\n" +
		"		<field name=\"field_300_00_013\">"+tax7+"</field>\n" +
		"		<field name=\"field_300_00_014_A\">"+baseamt8+"</field>\n" +
		"		<field name=\"field_300_00_014_B\">"+tax8+"</field>\n" +
		"		<field name=\"field_300_00_015_A\"/>\n" +
		"		<field name=\"field_300_00_015_B\"/>\n" +
		"		<field name=\"field_300_00_016\">"+baseamt8n+"</field>\n" +
		"		<field name=\"field_300_00_017_1_A\"/>\n" +
		"		<field name=\"field_300_00_017_1_B\"/>\n" +
		"		<field name=\"field_300_00_017_2_A\"/>\n" +
		"		<field name=\"field_300_00_017_2_B\"/>\n" +
		"		<field name=\"field_300_00_017_A\"/>\n" +
		"		<field name=\"field_300_00_017_B\"/>\n" +
		"		<field name=\"field_300_00_018\"/>\n" +
		"		<field name=\"field_300_00_019\"/>\n" +
		"		<field name=\"field_300_00_020\"/>\n" +
		"		<field name=\"field_300_00_021_A\"/>\n" +
		"		<field name=\"field_300_00_021_B\"/>\n" +
		"		<field name=\"field_300_00_022\">"+ baseamt8.add(baseamt8n) +"</field>\n" +
		"		<field name=\"field_300_00_023_A\"/>\n" +
		"		<field name=\"field_300_00_023_B\"/>\n" +
		"		<field name=\"field_300_00_024\"/>\n" +
		"		<field name=\"field_300_00_025\">"+tax8+"</field>\n" +
		"		<field name=\"field_300_00_026\"/>\n" +
		"		<field name=\"field_300_00_026_1\"/>\n" +
		"		<field name=\"field_300_00_026_2\"/>\n" +
		"		<field name=\"field_300_00_026_3\"/>\n" +
		"		<field name=\"field_300_00_027_1\"/>\n" +
		"		<field name=\"field_300_00_027_2\">"+tax8+"</field>\n" +
		"		<field name=\"field_300_00_027_3\"/>\n" +
		"		<field name=\"field_300_00_028_A\"/>\n" +
		"		<field name=\"field_300_00_028_B\"/>\n" +
		"		<field name=\"field_300_00_029\">"+((tax7.subtract(tax8)).compareTo(new BigDecimal(0)) == 1 ? (tax7.subtract(tax8)) :  "" )+"</field>\n" +
		"		<field name=\"field_300_00_029_1\"/>\n" +
		"		<field name=\"field_300_00_030\"/>" + ((tax7.subtract(tax8)).compareTo(new BigDecimal(0)) == -1 ? (tax7.subtract(tax8)) :  "" )+"</field>\n" +
		"		<field name=\"field_300_00_030_1\"/>\n" +
		"		<field name=\"field_300_00_031\"/>\n" +
		"		<field name=\"field_300_00_031_1\"/>\n" +
		"		<field name=\"field_300_00_032\"/>\n" +
		"		<field name=\"field_300_00_033\"/>\n" +
		"		<field name=\"field_300_00_033_1\"/>\n" +
		"		<field name=\"field_300_00_033_2_1\"/>\n" +
		"		<field name=\"field_300_00_033_2_2\"/>\n" +
		"		<field name=\"head_name\">Курганбекова Зарема Ерлановна</field>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"in_doc_number\"/>\n" +
		"		<field name=\"notice_date\"/>\n" +
		"		<field name=\"notice_number\"/>\n" +
		"		<field name=\"num\"/>\n" +
		"		<field name=\"number\">0005740</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"PaperFormsBarcode3\"/>\n" +
		"		<field name=\"PaperFormsBarcode4\"/>\n" +
		"		<field name=\"PaperFormsBarcode5\"/>\n" +
		"		<field name=\"payer_name1\">Акционерное Общество  \"Наци</field>\n" +
		"		<field name=\"payer_name2\">ональный инфокоммуникационный холди</field>\n" +
		"		<field name=\"payer_name3\">нг \"Зерде\"</field>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"post_date\"/>\n" +
		"		<field name=\"pril_1\"/>\n" +
		"		<field name=\"pril_2\"/>\n" +
		"		<field name=\"pril_3\"/>\n" +
		"		<field name=\"pril_4\"/>\n" +
		"		<field name=\"pril_5\"/>\n" +
		"		<field name=\"pril_6\"/>\n" +
		"		<field name=\"pril_7\">true</field>\n" +
		"		<field name=\"pril_8\">true</field>\n" +
		"		<field name=\"pril_9\"/>\n" +
		"		<field name=\"pril_10\"/>\n" +
		"		<field name=\"pril_11\"/>\n" +
		"		<field name=\"pril_12\"/>\n" +
		"		<field name=\"rating_auth_code\"/>\n" +
		"		<field name=\"receptor_name\"/>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"		<field name=\"series\">62001</field>\n" +
		"		<field name=\"submit_date\">"+startDate+"</field>\n" +
		"	</form>"+
		"	<form name=\"form_300_01\">\n" +
		"		<field name=\"field_300_01_001\"/>\n" +
		"		<field name=\"field_300_01_001_1\"/>\n" +
		"		<field name=\"field_300_01_001_2\"/>\n" +
		"		<field name=\"field_300_01_001_3\"/>\n" +
		"		<field name=\"field_300_01_002\"/>\n" +
		"		<field name=\"field_300_01_003\"/>\n" +
		"		<field name=\"field_300_01_004\"/>\n" +
		"		<field name=\"field_300_01_005\"/>\n" +
		"		<field name=\"field_300_01_006\"/>\n" +
		"		<field name=\"field_300_01_007\"/>\n" +
		"		<field name=\"field_300_01_008\"/>\n" +
		"		<field name=\"field_300_01_009\"/>\n" +
		"		<field name=\"field_300_01_010\"/>\n" +
		"		<field name=\"field_300_01_011\"/>\n" +
		"		<field name=\"field_300_01_012\"/>\n" +
		"		<field name=\"field_300_01_013\"/>\n" +
		"		<field name=\"field_300_01_014\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_02\">\n" +
		"		<field name=\"field_300_02_001\"/>\n" +
		"		<field name=\"field_300_02_002\"/>\n" +
		"		<field name=\"field_300_02_003\"/>\n" +
		"		<field name=\"field_300_02_004\"/>\n" +
		"		<field name=\"field_300_02_005\"/>\n" +
		"		<field name=\"field_300_02_006\"/>\n" +
		"		<field name=\"field_300_02_007\"/>\n" +
		"		<field name=\"field_300_02_007_1\"/>\n" +
		"		<field name=\"field_300_02_007_2\"/>\n" +
		"		<field name=\"field_300_02_007_3\"/>\n" +
		"		<field name=\"field_300_02_007_4\"/>\n" +
		"		<field name=\"field_300_02_007_5\"/>\n" +
		"		<field name=\"field_300_02_008\"/>\n" +
		"		<field name=\"field_300_02_009\"/>\n" +
		"		<field name=\"field_300_02_010\"/>\n" +
		"		<field name=\"field_300_02_011\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"PaperFormsBarcode2\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_03\">\n" +
		"		<field name=\"form_300_03_n_d_1\"/>\n" +
		"		<field name=\"form_300_03_n_d_2\"/>\n" +
		"		<field name=\"form_300_03_n_f_1\"/>\n" +
		"		<field name=\"form_300_03_n_f_2\"/>\n" +
		"		<field name=\"form_300_03_n_g_1\"/>\n" +
		"		<field name=\"form_300_03_n_g_2\"/>\n" +
		"		<field name=\"form_300_03_n_h_1\"/>\n" +
		"		<field name=\"form_300_03_n_h_2\"/>\n" +
		"		<field name=\"form_300_03_n_j_1\"/>\n" +
		"		<field name=\"form_300_03_n_j_2\"/>\n" +
		"		<field name=\"form_300_03_n_k_1\"/>\n" +
		"		<field name=\"form_300_03_n_k_2\"/>\n" +
		"		<field name=\"form_300_03_n_l_1\"/>\n" +
		"		<field name=\"form_300_03_n_l_2\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"page_number\"/>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"PaperFormsBarcode2\"/>\n" +
		"		<field name=\"PaperFormsBarcode3\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_04\">\n" +
		"		<field name=\"field_300_04_001_A\"/>\n" +
		"		<field name=\"field_300_04_001_A_1\"/>\n" +
		"		<field name=\"field_300_04_001_A_2\"/>\n" +
		"		<field name=\"field_300_04_001_A_3\"/>\n" +
		"		<field name=\"field_300_04_001_A_4\"/>\n" +
		"		<field name=\"field_300_04_001_A_5\"/>\n" +
		"		<field name=\"field_300_04_001_A_6\"/>\n" +
		"		<field name=\"field_300_04_001_A_7\"/>\n" +
		"		<field name=\"field_300_04_001_A_8\"/>\n" +
		"		<field name=\"field_300_04_001_A_9\"/>\n" +
		"		<field name=\"field_300_04_001_B\"/>\n" +
		"		<field name=\"field_300_04_001_B_1\"/>\n" +
		"		<field name=\"field_300_04_001_B_2\"/>\n" +
		"		<field name=\"field_300_04_001_B_3\"/>\n" +
		"		<field name=\"field_300_04_001_B_4\"/>\n" +
		"		<field name=\"field_300_04_001_B_5\"/>\n" +
		"		<field name=\"field_300_04_001_B_6\"/>\n" +
		"		<field name=\"field_300_04_001_B_7\"/>\n" +
		"		<field name=\"field_300_04_001_B_8\"/>\n" +
		"		<field name=\"field_300_04_001_B_9\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_05\">\n" +
		"		<field name=\"field_300_05_001\"/>\n" +
		"		<field name=\"field_300_05_002\"/>\n" +
		"		<field name=\"field_300_05_003\"/>\n" +
		"		<field name=\"field_300_05_004\"/>\n" +
		"		<field name=\"field_300_05_005\"/>\n" +
		"		<field name=\"field_300_05_006\"/>\n" +
		"		<field name=\"field_300_05_007\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_06\">\n" +
		"		<field name=\"field_300_06_001_A\"/>\n" +
		"		<field name=\"field_300_06_001_B\"/>\n" +
		"		<field name=\"field_300_06_002_A\"/>\n" +
		"		<field name=\"field_300_06_002_B\"/>\n" +
		"		<field name=\"field_300_06_003_A\"/>\n" +
		"		<field name=\"field_300_06_003_B\"/>\n" +
		"		<field name=\"field_300_06_004_A\"/>\n" +
		"		<field name=\"field_300_06_004_B\"/>\n" +
		"		<field name=\"field_300_06_005_A\"/>\n" +
		"		<field name=\"field_300_06_005_B\"/>\n" +
		"		<field name=\"field_300_06_006_A\"/>\n" +
		"		<field name=\"field_300_06_006_B\"/>\n" +
		"		<field name=\"field_300_06_007_A\"/>\n" +
		"		<field name=\"field_300_06_007_B\"/>\n" +
		"		<field name=\"field_300_06_008_A\"/>\n" +
		"		<field name=\"field_300_06_008_B\"/>\n" +
		"		<field name=\"field_300_06_009_A\"/>\n" +
		"		<field name=\"field_300_06_009_B\"/>\n" +
		"		<field name=\"field_300_06_010_A\"/>\n" +
		"		<field name=\"field_300_06_011_A\"/>\n" +
		"		<field name=\"field_300_06_012_A\"/>\n" +
		"		<field name=\"field_300_06_013_A\"/>\n" +
		"		<field name=\"field_300_06_014_A\"/>\n" +
		"		<field name=\"field_300_06_015_A\"/>\n" +
		"		<field name=\"field_300_06_016_A\"/>\n" +
		"		<field name=\"field_300_06_017_A\"/>\n" +
		"		<field name=\"field_300_06_018_A\"/>\n" +
		"		<field name=\"field_300_06_019_A\"/>\n" +
		"		<field name=\"field_300_06_020_A\"/>\n" +
		"		<field name=\"field_300_06_021_A\"/>\n" +
		"		<field name=\"field_300_06_022_A\"/>\n" +
		"		<field name=\"field_300_06_023_A\"/>\n" +
		"		<field name=\"field_300_06_024_A\"/>\n" +
		"		<field name=\"field_300_06_012_B\"/>\n" +
		"		<field name=\"field_300_06_013_B\"/>\n" +
		"		<field name=\"field_300_06_014_B\"/>\n" +
		"		<field name=\"field_300_06_015_B\"/>\n" +
		"		<field name=\"field_300_06_016_B\"/>\n" +
		"		<field name=\"field_300_06_017_B\"/>\n" +
		"		<field name=\"field_300_06_018_B\"/>\n" +
		"		<field name=\"field_300_06_019_B\"/>\n" +
		"		<field name=\"field_300_06_020_B\"/>\n" +
		"		<field name=\"field_300_06_021_B\"/>\n" +
		"		<field name=\"field_300_06_022_B\"/>\n" +
		"		<field name=\"field_300_06_023_B\"/>\n" +
		"		<field name=\"field_300_06_024_B\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"PaperFormsBarcode2\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n"		
				);
		
		//300.07-------------------------------------------

		BigDecimal h = new BigDecimal(0);
		BigDecimal i = new BigDecimal(0);
		BigDecimal j = new BigDecimal(0);
		
		for(line l: list30007){
			if(l.getTaxamt().compareTo(new BigDecimal(0)) == 1){
				h = h.add(l.taxbaseamt);
				i = i.add(l.taxamt);
				j = j.add(l.taxamt2);
			}
		}

		out.write("	<form name="+'"'+"form_300_07"+'"'+">\n");		
		out.write("		<field name="+'"'+"form_300_07_n_h_1"+'"'+">"+h+"</field>\n");
		out.write("		<field name="+'"'+"form_300_07_n_i_1"+'"'+">"+i+"</field>\n");
		out.write("		<field name="+'"'+"form_300_07_n_j_1"+'"'+">"+j+"</field>\n");
		out.write("		<field name="+'"'+"iin"+'"'+">080940016280</field>\n");
		out.write("		<field name="+'"'+"page_number"+'"'+">1</field>\n");
		out.write("		<field name="+'"'+"PaperFormsBarcode1"+'"'+"/>\n");
		out.write("		<field name="+'"'+"period_quarter"+'"'+">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n");
		out.write("		<field name="+'"'+"period_year"+'"'+">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n");
		out.write("		<field name="+'"'+"rnn"+'"'+">620200331655</field>\n");

		int row = 1;
		for(line l: list30007){
			if(l.getTaxamt().compareTo(new BigDecimal(0)) == 1){
				row++;
				
				out.write("			<row>\n");
				out.write("				<field name="+'"'+"form_300_07_n_a"+'"'+">"+ TextParser.getNumberWithPreZero(row, 8) +"</field>\n"); //TextParser.getNumberWithPreZero(row, 8) 
				out.write("				<field name="+'"'+"form_300_08_n_b"+'"'+">"+""+"</field>\n");//(l.c_bpartner_id == null ? "" : l.c_bpartner_id)+ 
				out.write("				<field name="+'"'+"form_300_07_n_c"+'"'+">" + (l.fi_tin == null ? "" : l.fi_tin) + "</field>\n"); 
				out.write("				<field name="+'"'+"form_300_07_n_d"+'"'+">" + (l.fi_bin_iin == null ? "": l.fi_bin_iin) + "</field>\n");
				out.write("				<field name="+'"'+"form_300_07_n_e"+'"'+">"+(l.documentno == null ? "":l.documentno)+"</field>\n");
				out.write("				<field name="+'"'+"form_300_07_n_f"+'"'+">"+(l.dateacct == null ? "":l.dateacct)+"</field>\n"); 
				out.write("				<field name="+'"'+"form_300_07_n_g"+'"'+"/>\n"); 
				out.write("				<field name="+'"'+"form_300_07_n_h"+'"'+">" + l.taxbaseamt.toString() + "</field>\n"); 
				out.write("				<field name="+'"'+"form_300_07_n_i"+'"'+">" + l.taxamt.toString() + "</field>\n"); 
				out.write("				<field name="+'"'+"form_300_07_n_j"+'"'+">" + l.taxamt2.toString() + "</field>\n");
				out.write("			</row>\n"); 
			}
		}
		out.write("	</form>\n");		
		
		//300.08-------------------------------------------
		h = new BigDecimal(0);
		i = new BigDecimal(0);
		j = new BigDecimal(0);
		list30008 = getInvoices30008();		
		for(line l: list30008){
			if(l.getTaxamt().compareTo(new BigDecimal(0)) == 1){
				h = h.add(l.taxbaseamt);
				i = i.add(l.taxamt);
				j = j.add(l.taxamt2);
			}
		}

		out.write("		<form name="+'"'+"form_300_08"+'"'+">\n");		
		out.write("			<field name="+'"'+"form_300_08_n_h_1"+'"'+">"+h+"</field>\n");
		out.write("			<field name="+'"'+"form_300_08_n_i_1"+'"'+">"+i+"</field>\n");
		out.write("			<field name="+'"'+"form_300_08_n_j_1"+'"'+">"+j+"</field>\n");
		out.write("			<field name="+'"'+"iin"+'"'+">080940016280</field>\n");
		out.write("			<field name="+'"'+"page_number"+'"'+">1</field>\n");
		out.write("			<field name="+'"'+"PaperFormsBarcode1"+'"'+"/>\n");
		out.write("			<field name="+'"'+"period_quarter"+'"'+">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n");
		out.write("			<field name="+'"'+"period_year"+'"'+">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n");
		out.write("			<field name="+'"'+"rnn"+'"'+">620200331655</field>\n");

		row = 1;
		for(line l: list30008){
			if(l.getTaxamt().compareTo(new BigDecimal(0)) == 1){
				row++;
				out.write("		<row>\n");
				out.write("			<field name="+'"'+"form_300_08_n_a"+'"'+">"+ TextParser.getNumberWithPreZero(row, 8) +"</field>\n"); //TextParser.getNumberWithPreZero(row, 8) 
				out.write("			<field name="+'"'+"form_300_08_n_b"+'"'+">"+""+"</field>\n");//(l.c_bpartner_id == null ? "" : l.c_bpartner_id)+ 
				out.write("			<field name="+'"'+"form_300_08_n_c"+'"'+">" + (l.fi_tin == null ? "" : l.fi_tin) + "</field>\n"); 
				out.write("			<field name="+'"'+"form_300_08_n_d"+'"'+">" + (l.fi_bin_iin == null ? "": l.fi_bin_iin) + "</field>\n");
				out.write("			<field name="+'"'+"form_300_08_n_e"+'"'+">"+(l.documentno == null ? "":l.documentno)+"</field>\n");
				out.write("			<field name="+'"'+"form_300_08_n_f"+'"'+">"+(l.dateacct == null ? "":l.dateacct)+"</field>\n"); 
				out.write("			<field name="+'"'+"form_300_08_n_g"+'"'+"/>\n"); 
				out.write("			<field name="+'"'+"form_300_08_n_h"+'"'+">" + l.taxbaseamt + "</field>\n"); 
				out.write("			<field name="+'"'+"form_300_08_n_i"+'"'+">" + l.taxamt + "</field>\n"); 
				out.write("			<field name="+'"'+"form_300_08_n_j"+'"'+">" + l.taxamt2 + "</field>\n");
				out.write("		</row>\n");
			}
		}
		out.write("		</form>\n");		
		
		out.write(	
		"	<form name=\"form_300_09\">\n" +
		"		<field name=\"form_300_09_n_g_1\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"page_number\"/>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"PaperFormsBarcode2\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_10\">\n" +
		"		<field name=\"form_300_10_n_g_1\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"page_number\"/>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"PaperFormsBarcode2\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_11\">\n" +
		"		<field name=\"field_300_11_001\"/>\n" +
		"		<field name=\"field_300_11_001_01\"/>\n" +
		"		<field name=\"field_300_11_001_01_1_1\"/>\n" +
		"		<field name=\"field_300_11_001_01_1_2\"/>\n" +
		"		<field name=\"field_300_11_001_01_1_3\"/>\n" +
		"		<field name=\"field_300_11_001_01_1_4\"/>\n" +
		"		<field name=\"field_300_11_001_01_1_5\"/>\n" +
		"		<field name=\"field_300_11_001_01_12_1\"/>\n" +
		"		<field name=\"field_300_11_001_01_12_2\"/>\n" +
		"		<field name=\"field_300_11_001_01_12_3\"/>\n" +
		"		<field name=\"field_300_11_001_01_12_4\"/>\n" +
		"		<field name=\"field_300_11_001_01_12_5\"/>\n" +
		"		<field name=\"field_300_11_001_01_2_1\"/>\n" +
		"		<field name=\"field_300_11_001_01_2_2\"/>\n" +
		"		<field name=\"field_300_11_001_01_2_3\"/>\n" +
		"		<field name=\"field_300_11_001_01_2_4\"/>\n" +
		"		<field name=\"field_300_11_001_01_2_5\"/>\n" +
		"		<field name=\"field_300_11_001_01_3_1\"/>\n" +
		"		<field name=\"field_300_11_001_01_3_2\"/>\n" +
		"		<field name=\"field_300_11_001_01_3_3\"/>\n" +
		"		<field name=\"field_300_11_001_01_3_4\"/>\n" +
		"		<field name=\"field_300_11_001_01_3_5\"/>\n" +
		"		<field name=\"field_300_11_001_01_34_1\"/>\n" +
		"		<field name=\"field_300_11_001_01_34_2\"/>\n" +
		"		<field name=\"field_300_11_001_01_34_3\"/>\n" +
		"		<field name=\"field_300_11_001_01_34_4\"/>\n" +
		"		<field name=\"field_300_11_001_01_34_5\"/>\n" +
		"		<field name=\"field_300_11_001_01_4_1\"/>\n" +
		"		<field name=\"field_300_11_001_01_4_2\"/>\n" +
		"		<field name=\"field_300_11_001_01_4_3\"/>\n" +
		"		<field name=\"field_300_11_001_01_4_4\"/>\n" +
		"		<field name=\"field_300_11_001_01_4_5\"/>\n" +
		"		<field name=\"field_300_11_001_02\"/>\n" +
		"		<field name=\"field_300_11_001_02_1_1\"/>\n" +
		"		<field name=\"field_300_11_001_02_1_2\"/>\n" +
		"		<field name=\"field_300_11_001_02_1_3\"/>\n" +
		"		<field name=\"field_300_11_001_02_1_4\"/>\n" +
		"		<field name=\"field_300_11_001_02_1_5\"/>\n" +
		"		<field name=\"field_300_11_001_02_12_1\"/>\n" +
		"		<field name=\"field_300_11_001_02_12_2\"/>\n" +
		"		<field name=\"field_300_11_001_02_12_3\"/>\n" +
		"		<field name=\"field_300_11_001_02_12_4\"/>\n" +
		"		<field name=\"field_300_11_001_02_12_5\"/>\n" +
		"		<field name=\"field_300_11_001_02_2_1\"/>\n" +
		"		<field name=\"field_300_11_001_02_2_2\"/>\n" +
		"		<field name=\"field_300_11_001_02_2_3\"/>\n" +
		"		<field name=\"field_300_11_001_02_2_4\"/>\n" +
		"		<field name=\"field_300_11_001_02_2_5\"/>\n" +
		"		<field name=\"field_300_11_001_02_3_1\"/>\n" +
		"		<field name=\"field_300_11_001_02_3_2\"/>\n" +
		"		<field name=\"field_300_11_001_02_3_3\"/>\n" +
		"		<field name=\"field_300_11_001_02_3_4\"/>\n" +
		"		<field name=\"field_300_11_001_02_3_5\"/>\n" +
		"		<field name=\"field_300_11_001_02_34_1\"/>\n" +
		"		<field name=\"field_300_11_001_02_34_2\"/>\n" +
		"		<field name=\"field_300_11_001_02_34_3\"/>\n" +
		"		<field name=\"field_300_11_001_02_34_4\"/>\n" +
		"		<field name=\"field_300_11_001_02_34_5\"/>\n" +
		"		<field name=\"field_300_11_001_02_4_1\"/>\n" +
		"		<field name=\"field_300_11_001_02_4_2\"/>\n" +
		"		<field name=\"field_300_11_001_02_4_3\"/>\n" +
		"		<field name=\"field_300_11_001_02_4_4\"/>\n" +
		"		<field name=\"field_300_11_001_02_4_5\"/>\n" +
		"		<field name=\"field_300_11_001_03\"/>\n" +
		"		<field name=\"field_300_11_001_03_1_1\"/>\n" +
		"		<field name=\"field_300_11_001_03_1_2\"/>\n" +
		"		<field name=\"field_300_11_001_03_1_3\"/>\n" +
		"		<field name=\"field_300_11_001_03_1_4\"/>\n" +
		"		<field name=\"field_300_11_001_03_1_5\"/>\n" +
		"		<field name=\"field_300_11_001_03_12_1\"/>\n" +
		"		<field name=\"field_300_11_001_03_12_2\"/>\n" +
		"		<field name=\"field_300_11_001_03_12_3\"/>\n" +
		"		<field name=\"field_300_11_001_03_12_4\"/>\n" +
		"		<field name=\"field_300_11_001_03_12_5\"/>\n" +
		"		<field name=\"field_300_11_001_03_2_1\"/>\n" +
		"		<field name=\"field_300_11_001_03_2_2\"/>\n" +
		"		<field name=\"field_300_11_001_03_2_3\"/>\n" +
		"		<field name=\"field_300_11_001_03_2_4\"/>\n" +
		"		<field name=\"field_300_11_001_03_2_5\"/>\n" +
		"		<field name=\"field_300_11_001_03_3_1\"/>\n" +
		"		<field name=\"field_300_11_001_03_3_2\"/>\n" +
		"		<field name=\"field_300_11_001_03_3_3\"/>\n" +
		"		<field name=\"field_300_11_001_03_3_4\"/>\n" +
		"		<field name=\"field_300_11_001_03_3_5\"/>\n" +
		"		<field name=\"field_300_11_001_03_34_1\"/>\n" +
		"		<field name=\"field_300_11_001_03_34_2\"/>\n" +
		"		<field name=\"field_300_11_001_03_34_3\"/>\n" +
		"		<field name=\"field_300_11_001_03_34_4\"/>\n" +
		"		<field name=\"field_300_11_001_03_34_5\"/>\n" +
		"		<field name=\"field_300_11_001_03_4_1\"/>\n" +
		"		<field name=\"field_300_11_001_03_4_2\"/>\n" +
		"		<field name=\"field_300_11_001_03_4_3\"/>\n" +
		"		<field name=\"field_300_11_001_03_4_4\"/>\n" +
		"		<field name=\"field_300_11_001_03_4_5\"/>\n" +
		"		<field name=\"field_300_11_001_04\"/>\n" +
		"		<field name=\"field_300_11_001_04_1_1\"/>\n" +
		"		<field name=\"field_300_11_001_04_1_2\"/>\n" +
		"		<field name=\"field_300_11_001_04_1_3\"/>\n" +
		"		<field name=\"field_300_11_001_04_1_4\"/>\n" +
		"		<field name=\"field_300_11_001_04_1_5\"/>\n" +
		"		<field name=\"field_300_11_001_04_12_1\"/>\n" +
		"		<field name=\"field_300_11_001_04_12_2\"/>\n" +
		"		<field name=\"field_300_11_001_04_12_3\"/>\n" +
		"		<field name=\"field_300_11_001_04_12_4\"/>\n" +
		"		<field name=\"field_300_11_001_04_12_5\"/>\n" +
		"		<field name=\"field_300_11_001_04_2_1\"/>\n" +
		"		<field name=\"field_300_11_001_04_2_2\"/>\n" +
		"		<field name=\"field_300_11_001_04_2_3\"/>\n" +
		"		<field name=\"field_300_11_001_04_2_4\"/>\n" +
		"		<field name=\"field_300_11_001_04_2_5\"/>\n" +
		"		<field name=\"field_300_11_001_04_3_1\"/>\n" +
		"		<field name=\"field_300_11_001_04_3_2\"/>\n" +
		"		<field name=\"field_300_11_001_04_3_3\"/>\n" +
		"		<field name=\"field_300_11_001_04_3_4\"/>\n" +
		"		<field name=\"field_300_11_001_04_3_5\"/>\n" +
		"		<field name=\"field_300_11_001_04_34_1\"/>\n" +
		"		<field name=\"field_300_11_001_04_34_2\"/>\n" +
		"		<field name=\"field_300_11_001_04_34_3\"/>\n" +
		"		<field name=\"field_300_11_001_04_34_4\"/>\n" +
		"		<field name=\"field_300_11_001_04_34_5\"/>\n" +
		"		<field name=\"field_300_11_001_04_4_1\"/>\n" +
		"		<field name=\"field_300_11_001_04_4_2\"/>\n" +
		"		<field name=\"field_300_11_001_04_4_3\"/>\n" +
		"		<field name=\"field_300_11_001_04_4_4\"/>\n" +
		"		<field name=\"field_300_11_001_04_4_5\"/>\n" +
		"		<field name=\"field_300_11_001_05\"/>\n" +
		"		<field name=\"field_300_11_001_05_1_1\"/>\n" +
		"		<field name=\"field_300_11_001_05_1_2\"/>\n" +
		"		<field name=\"field_300_11_001_05_1_3\"/>\n" +
		"		<field name=\"field_300_11_001_05_1_4\"/>\n" +
		"		<field name=\"field_300_11_001_05_1_5\"/>\n" +
		"		<field name=\"field_300_11_001_05_12_1\"/>\n" +
		"		<field name=\"field_300_11_001_05_12_2\"/>\n" +
		"		<field name=\"field_300_11_001_05_12_3\"/>\n" +
		"		<field name=\"field_300_11_001_05_12_4\"/>\n" +
		"		<field name=\"field_300_11_001_05_12_5\"/>\n" +
		"		<field name=\"field_300_11_001_05_2_1\"/>\n" +
		"		<field name=\"field_300_11_001_05_2_2\"/>\n" +
		"		<field name=\"field_300_11_001_05_2_3\"/>\n" +
		"		<field name=\"field_300_11_001_05_2_4\"/>\n" +
		"		<field name=\"field_300_11_001_05_2_5\"/>\n" +
		"		<field name=\"field_300_11_001_05_3_1\"/>\n" +
		"		<field name=\"field_300_11_001_05_3_2\"/>\n" +
		"		<field name=\"field_300_11_001_05_3_3\"/>\n" +
		"		<field name=\"field_300_11_001_05_3_4\"/>\n" +
		"		<field name=\"field_300_11_001_05_3_5\"/>\n" +
		"		<field name=\"field_300_11_001_05_34_1\"/>\n" +
		"		<field name=\"field_300_11_001_05_34_2\"/>\n" +
		"		<field name=\"field_300_11_001_05_34_3\"/>\n" +
		"		<field name=\"field_300_11_001_05_34_4\"/>\n" +
		"		<field name=\"field_300_11_001_05_34_5\"/>\n" +
		"		<field name=\"field_300_11_001_05_4_1\"/>\n" +
		"		<field name=\"field_300_11_001_05_4_2\"/>\n" +
		"		<field name=\"field_300_11_001_05_4_3\"/>\n" +
		"		<field name=\"field_300_11_001_05_4_4\"/>\n" +
		"		<field name=\"field_300_11_001_05_4_5\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"	</form>\n" +
		"	<form name=\"form_300_12\">\n" +
		"		<field name=\"field_300_12_001_A\"/>\n" +
		"		<field name=\"field_300_12_001_B\"/>\n" +
		"		<field name=\"field_300_12_002\"/>\n" +
		"		<field name=\"field_300_12_003_A\"/>\n" +
		"		<field name=\"field_300_12_003_B\"/>\n" +
		"		<field name=\"field_300_12_004_A\"/>\n" +
		"		<field name=\"field_300_12_004_B\"/>\n" +
		"		<field name=\"field_300_12_005\"/>\n" +
		"		<field name=\"field_300_12_006\"/>\n" +
		"		<field name=\"field_300_12_007\"/>\n" +
		"		<field name=\"field_300_12_008\"/>\n" +
		"		<field name=\"field_300_12_009\"/>\n" +
		"		<field name=\"field_300_12_010\"/>\n" +
		"		<field name=\"field_300_12_011\"/>\n" +
		"		<field name=\"field_300_12_012\"/>\n" +
		"		<field name=\"field_300_12_013_A\"/>\n" +
		"		<field name=\"field_300_12_013_B\"/>\n" +
		"		<field name=\"field_300_12_014_A\"/>\n" +
		"		<field name=\"field_300_12_014_B\"/>\n" +
		"		<field name=\"field_300_12_015\"/>\n" +
		"		<field name=\"field_300_12_016_01_A\"/>\n" +
		"		<field name=\"field_300_12_016_01_B\"/>\n" +
		"		<field name=\"field_300_12_016_02_A\"/>\n" +
		"		<field name=\"field_300_12_016_02_B\"/>\n" +
		"		<field name=\"field_300_12_016_A\"/>\n" +
		"		<field name=\"field_300_12_016_B\"/>\n" +
		"		<field name=\"field_300_12_017\"/>\n" +
		"		<field name=\"field_300_12_018\"/>\n" +
		"		<field name=\"field_300_12_019\"/>\n" +
		"		<field name=\"field_300_12_020\"/>\n" +
		"		<field name=\"field_300_12_021_A\"/>\n" +
		"		<field name=\"field_300_12_021_B\"/>\n" +
		"		<field name=\"field_300_12_022\"/>\n" +
		"		<field name=\"field_300_12_023\"/>\n" +
		"		<field name=\"field_300_12_024\"/>\n" +
		"		<field name=\"field_300_12_024_01\"/>\n" +
		"		<field name=\"field_300_12_024_02\"/>\n" +
		"		<field name=\"field_300_12_024_03\"/>\n" +
		"		<field name=\"field_300_12_025_01\"/>\n" +
		"		<field name=\"field_300_12_025_02\"/>\n" +
		"		<field name=\"field_300_12_025_03\"/>\n" +
		"		<field name=\"field_300_12_026_A\"/>\n" +
		"		<field name=\"field_300_12_026_B\"/>\n" +
		"		<field name=\"field_300_12_027\"/>\n" +
		"		<field name=\"field_300_12_027_01\"/>\n" +
		"		<field name=\"field_300_12_028\"/>\n" +
		"		<field name=\"field_300_12_028_01\"/>\n" +
		"		<field name=\"iin\">080940016280</field>\n" +
		"		<field name=\"PaperFormsBarcode1\"/>\n" +
		"		<field name=\"PaperFormsBarcode2\"/>\n" +
		"		<field name=\"period_quarter\">"+Integer.valueOf(DataUtils.getQuarter(startDate)).toString()+"</field>\n" +
		"		<field name=\"period_year\">"+ Integer.valueOf(startDate.getYear() + 1900).toString() +"</field>\n" +
		"		<field name=\"rnn\">620200331655</field>\n" +
		"		<field name=\"vid_4a\"/>\n" +
		"		<field name=\"vid_4b\"/>\n" +
		"	</form>\n" +
		"</fno>"
		);
		
		
		
		
		out.flush();
		out.close();
		fos.close();
	}
	   
	   return Msg.translate(m_ctx, "Success");
   } 
   
   private String toExcel() throws Exception{
       // Types of cells for
       Label label;
       Number number;
       BigDecimal sum30007 = new BigDecimal(0);
       BigDecimal sum30007nds = new BigDecimal(0);
       BigDecimal sum30008 = new BigDecimal(0);
       BigDecimal sum30008nds = new BigDecimal(0);
       BigDecimal sum300 = new BigDecimal(0);
       BigDecimal sum300nds = new BigDecimal(0);
       // There are checks for a specified pattern
       String templateName = "FI_DeclarationOfVAT300_tmpl.xls";
       //
       MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
       if(entry == null)
           throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
  
       File templateCopy = new File(Util.localFilePath + "FI_DeclarationOfVAT300.xls");
       File temporaryFile = entry.getFile(Util.localFilePath + templateName);
       Workbook tableBook = null;
       WritableWorkbook copy = null;
       WritableSheet sheet = null;
       WritableSheet sheet30007 = null;
       WritableSheet sheet30008 = null;
       WritableSheet sheet4 = null;
       WritableSheet sheet5 = null;
       WritableSheet sheet6 = null;
       WritableSheet sheet7 = null;
       try {
            tableBook = Workbook.getWorkbook(temporaryFile);
            copy = Workbook.createWorkbook(templateCopy, tableBook);
            sheet = copy.getSheet(0);
            sheet30007 = copy.getSheet(1);
            sheet30008 = copy.getSheet(2);
            sheet4 = copy.getSheet(3);
            sheet5 = copy.getSheet(4);
            sheet6 = copy.getSheet(5);
            sheet7 = copy.getSheet(6);

       } catch (Exception e) {
           ADialog.error(999, null, "Error tableBook. " + e.getMessage());
           return "Error tableBook.";
       }
       

//
//      ExcelCell cellStart =  Utils.getCellStart(tableBook,">>");
    // Style for value column
      WritableCellFormat valuestyle = new WritableCellFormat();
      valuestyle.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
      valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
      valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
      valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
      valuestyle.setAlignment(Alignment.RIGHT);
      valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
      valuestyle.setWrap(true);

      WritableCellFormat vs = new WritableCellFormat();
      vs.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
      vs.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
      vs.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
      vs.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
      vs.setAlignment(Alignment.LEFT);
      vs.setVerticalAlignment(VerticalAlignment.CENTRE);
      vs.setWrap(true);      

      WritableCellFormat vsc = new WritableCellFormat();
      vsc.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
      vsc.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
      vsc.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
      vsc.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
      vsc.setAlignment(Alignment.CENTRE);
      vsc.setVerticalAlignment(VerticalAlignment.CENTRE);
      vsc.setWrap(true);      
      
      //300.08----------------------------------------------------------------------
      List<line> l = getInvoices30008();
      int count = l.size();   
      int row = 7;
      
      String caption =  "Реестр счетов-фактур \n"
		+ " по приобретенным товарам, работам, услугам в течение отчетного налогового периода \n"
		+ " c " + new java.text.SimpleDateFormat("dd.MM.yyyy").format(startDate).toString() + " по " +  new java.text.SimpleDateFormat("dd.MM.yyyy").format(endDate).toString();
      WritableCellFormat captionStyle = new WritableCellFormat();
      captionStyle.setAlignment(Alignment.CENTRE);
      captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
      
      label = new Label(0, 0, caption, captionStyle);
      sheet30008.addCell(label);

      label = new Label(0, 3, "РНН " + bp_org.getFI_TIN(), vs);
      sheet30008.addCell(label);
      label = new Label(2, 3, "ИИН(БИН) " + bp_org.getFI_BIN_IIN(), vs);
      sheet30008.addCell(label);

      
      for(int j=0; j < count; j++){
    	  
    	line li = l.get(j);
    	if(li.getTaxamt().compareTo(new BigDecimal(0)) == 1){
    		
	        label = new Label(0, row-1, Integer.valueOf(j+1).toString(), valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(1, row-1, "", valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(2, row-1, li.fi_tin, valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(3, row-1, li.fi_bin_iin, valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(4, row-1, li.documentno, valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(5, row-1, li.dateinvoiced, valuestyle);
	        sheet30008.addCell(label);
	
	        label = new Label(6, row-1, "", valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(7, row-1, "", valuestyle);
	        sheet30008.addCell(label);        
	        
	        label = new Label(8, row-1, li.taxbaseamt.toString(), valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(9, row-1, li.taxamt.toString(), valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(10, row-1, li.taxamt2.toString(), valuestyle);
	        sheet30008.addCell(label);
	        
	        label = new Label(9, 3, li.period, valuestyle);
	        sheet30008.addCell(label);
	        
	        row++;
	        
	        sum30008 = sum30008.add(li.taxbaseamt);
	        sum30008nds = sum30008nds.add(li.taxamt);
    	}
      }      
      
      label = new Label(3, 1, bp_org.getFI_TIN(), vsc);
      sheet5.addCell(label);
      label = new Label(19, 1, bp_org.getFI_BIN_IIN(), vsc);
      sheet5.addCell(label);
//      label = new Label(9, 11, bp_org.getName(), vsc);
//      sheet5.addCell(label);
      
      label = new Label(15, 30, sum30008.toString(), valuestyle);
      sheet5.addCell(label);
//      label = new Label(15, 73, sum30008.toString(), valuestyle);
//      sheet5.addCell(label);      
      label = new Label(29, 30, sum30008nds.toString(), valuestyle);
      sheet5.addCell(label);
      

      
      // 300.07------------------------------------------------      
      caption =  "Реестр счетов-фактур \n"
		+ " по реализованным товарам, работам, услугам в течение отчетного налогового периода \n"
		+ " c " + new java.text.SimpleDateFormat("dd.MM.yyyy").format(startDate).toString() + " по " +  new java.text.SimpleDateFormat("dd.MM.yyyy").format(endDate).toString();
      captionStyle = new WritableCellFormat();
      captionStyle.setAlignment(Alignment.CENTRE);
      captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
      
      label = new Label(0, 0, caption, captionStyle);
      sheet30007.addCell(label);

      label = new Label(0, 0, caption, captionStyle);
      sheet30007.addCell(label);

      label = new Label(0, 3, "РНН " + bp_org.getFI_TIN(), vs);
      sheet30007.addCell(label);
      label = new Label(2, 3, "ИИН(БИН) " + bp_org.getFI_BIN_IIN(), vs);
      sheet30007.addCell(label);      
      
      l = getInvoices30007();
      count = l.size();  
      row = 7;
      
      for(int j=0; j < count; j++){
    	  line li = l.get(j);
    	  
    	  if(li.getTaxamt().compareTo(new BigDecimal(0)) == 1){
    		  
	    	  
	          label = new Label(0, row-1, Integer.valueOf(j+1).toString(), valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(1, row-1, "", valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(2, row-1, li.fi_tin, valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(3, row-1, li.fi_bin_iin, valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(4, row-1, li.documentno, valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(5, row-1, li.dateinvoiced, valuestyle);
	          sheet30007.addCell(label);
	
	          label = new Label(6, row-1, "", valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(7, row-1, "", valuestyle);
	          sheet30007.addCell(label);        
	          
	          label = new Label(8, row-1, li.taxbaseamt.toString(), valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(9, row-1, li.taxamt.toString(), valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(10, row-1, li.taxamt2.toString(), valuestyle);
	          sheet30007.addCell(label);
	          
	          label = new Label(9, 3, li.period, valuestyle);
	          sheet30007.addCell(label);
	          
	          row++;
	     	  
	          sum30007 = sum30007.add(li.taxbaseamt);
	          sum30007nds = sum30007nds.add(li.taxamt);
    	  }
        }            

      
      label = new Label(3, 9, bp_org.getFI_TIN(), vsc);
      sheet4.addCell(label);
      label = new Label(21, 9, bp_org.getFI_BIN_IIN(), vsc);
      sheet4.addCell(label);
      label = new Label(9, 11, bp_org.getDescription(), vsc);
      sheet4.addCell(label);
      label = new Label(9, 45, bp_org.getFI_SeriesOfVATCertificate(), vsc);
      sheet4.addCell(label);
      label = new Label(14, 45, bp_org.getFI_NumberOfVATCertificate(), vsc);
      sheet4.addCell(label);

      
      label = new Label(15, 53, sum30007.toString(), valuestyle);
      sheet4.addCell(label);
      label = new Label(15, 73, sum30007.toString(), valuestyle);
      sheet4.addCell(label);      
      label = new Label(29, 53, sum30007nds.toString(), valuestyle);
      sheet4.addCell(label);
      
      label = new Label(3, 1, bp_org.getFI_TIN(), vsc);
      sheet6.addCell(label);
      label = new Label(19, 1, bp_org.getFI_BIN_IIN(), vsc);
      sheet6.addCell(label);

      label = new Label(3, 1, bp_org.getFI_TIN(), vsc);
      sheet7.addCell(label);
      label = new Label(19, 1, bp_org.getFI_BIN_IIN(), vsc);
      sheet7.addCell(label);

//    ///  
      copy.write();
      copy.close();
      tableBook.close();
      temporaryFile.delete();
//      
        try{    
             String fileName = Util.localFilePath + "FI_DeclarationOfVAT300.xls";
             String command = "excel \""+fileName+"\"";
             Runtime.getRuntime().exec("cmd /c start "+command);
         }catch(Exception e){
             e.printStackTrace();
         }

        return Msg.translate(m_ctx, "Success");
   } 
   
   private List<line> getInvoices30008(){
	   
       List<line> list = new ArrayList<line>();
       //
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       
       String sql = 
//    		   " SELECT  \n" +
//    				   "row_number() over (order by invoice.c_invoice_id)  \n" +
////    				   ",invoice.c_invoice_id\n" +
//    				   ",bpartner.c_bpartner_id  \n" +
//    				   ",bpartner.fi_tin  \n" +
//    				   ",bpartner.fi_bin_iin  \n" +
//    				   ",invoice.fi_invoiceno	 \n" +
//    				   ",to_char(invoice.dateinvoiced,'dd.mm.yyyy') dateinvoiced  \n" +
//    				   ",to_char(invoice.dateacct,'dd.mm.yyyy') dateacct  \n" +
//    				   ",doctype.name  \n" +
//    				   ",invoicetax.taxbaseamt taxbaseamt  \n" +
//    				   ",invoicetax.taxamt taxamt  \n" +
//    				   ",invoicetax.taxamt taxamt2  , ('квартал ' || case      				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 1 then 1      				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 2 then 1  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 3 then 1  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 4 then 2  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 5 then 2  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 6 then 2  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 7 then 3  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 8 then 3  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 9 then 3  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 10 then 4  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 11 then 4  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 12 then 4  end  || ' год ' || cast(EXTRACT(YEAR from invoice.dateinvoiced) as int)) period  \n" +
//    				   "FROM  adempiere.c_invoice invoice  \n" +
//    				   "LEFT JOIN adempiere.c_invoicetax invoicetax on invoicetax.c_invoice_id = invoice.c_invoice_id  \n" +
//    				   "LEFT JOIN adempiere.c_doctype doctype on doctype.c_doctype_id = invoice.c_doctype_id  \n" +
//    				   "LEFT JOIN adempiere.c_bpartner bpartner on bpartner.c_bpartner_id = invoice.c_bpartner_id  \n" +
//    				   "inner join adempiere.fact_acct fa on fa.record_id = invoice.c_invoice_id\n" +
//    				   "inner join adempiere.c_elementvalue ce on fa.account_id = ce.c_elementvalue_id \n" +
//    				   "WHERE doctype.c_doctype_id = 1000005  \n" +
//    				   "and invoice.dateacct BETWEEN ? and ?  \n" +
//    				   "and invoice.dateinvoiced BETWEEN ? and ?  \n" +
//    				   "and invoice.docstatus = 'CO' \n" +
//    				   "and invoicetax.taxamt > 0  \n" +
//    				   "and fa.ad_table_id = 318\n" +
//    				   "and fa.line_id is not null\n" +
//    				   "and ce.value not like '721001'\n" +
//    				   "order by invoice.c_invoice_id"    		   
    		   	" SELECT "
    			+ " row_number() over (order by invoice.c_invoice_id) "
    			+ " ,bpartner.c_bpartner_id "
    			+ " ,bpartner.fi_tin "
    			+ " ,bpartner.fi_bin_iin "
    			+ " ,invoice.fi_invoiceno	"
    			+ " ,to_char(invoice.dateinvoiced,'dd.mm.yyyy') dateinvoiced "
    			+ " ,to_char(invoice.dateacct,'dd.mm.yyyy') dateacct "
    			+ " ,doctype.name "
    			+ " ,invoicetax.taxbaseamt taxbaseamt "
    			+ " ,invoicetax.taxamt taxamt "
    			+ " ,invoicetax.taxamt taxamt2 "	
    			
//    			+ " ,to_char(invoicetax.taxbaseamt,'9 999 999 999D99') taxbaseamt "
//    			+ " ,to_char(invoicetax.taxamt,'999 999 999 999D99') taxamt "
//    			+ " ,to_char(invoicetax.taxamt,'999 999 999 999D99') taxamt2 "	
    			+ " , ('квартал ' || case "
    			+ "     				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 1 then 1 "
    			+ "     				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 2 then 1 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 3 then 1 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 4 then 2 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 5 then 2 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 6 then 2 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 7 then 3 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 8 then 3 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 9 then 3 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 10 then 4 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 11 then 4 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 12 then 4 "
    			+ " end "
    			+ " || ' год ' || cast(EXTRACT(YEAR from invoice.dateinvoiced) as int)) period "
    			+ " FROM "
    			+ " c_invoice invoice "
    			+ " LEFT JOIN c_invoicetax invoicetax on invoicetax.c_invoice_id = invoice.c_invoice_id "
    			+ " LEFT JOIN c_doctype doctype on doctype.c_doctype_id = invoice.c_doctype_id "
    			+ " LEFT JOIN c_bpartner bpartner on bpartner.c_bpartner_id = invoice.c_bpartner_id "
    			+ " WHERE doctype.c_doctype_id = 1000005 "
    			+ " and invoice.dateacct BETWEEN ? and ? "
    			+ " and invoice.dateinvoiced BETWEEN ? and ? "
    			+ " and invoice.docstatus = 'CO' " 
//    			+ "and invoicetax.taxamt > 0 "
    			+ " order by invoice.dateacct";
				;
       //
           try
           {
               pstmt = DB.prepareStatement(sql,null);

               pstmt.setTimestamp(1, startDate);
               pstmt.setTimestamp(2, endDate);
               pstmt.setTimestamp(3, startDate2);
               pstmt.setTimestamp(4, endDate2);
               
               rs = pstmt.executeQuery();    
               while (rs.next ())
               {
            	   line l = new line();
                   l.setRow(String.valueOf(rs.getInt(1)));
                   l.setC_bpartner_id(String.valueOf(rs.getInt(2)));
                   l.setFi_tin(rs.getString(3));
                   l.setFi_bin_iin(rs.getString(4));
                   l.setDocumentno(rs.getString(5));
                   l.setDateinvoiced(rs.getString(6));
                   l.setDateacct(rs.getString(7));
                   l.setName(rs.getString(8));
                   l.setTaxbaseamt(rs.getBigDecimal(9));
                   l.setTaxamt(rs.getBigDecimal(10));
                   l.setTaxamt2(rs.getBigDecimal(11));
                   //l.setCaption(rs.getString(11));
                   l.setPeriod(rs.getString(12));

                   l.setTaxbaseamt(l.getTaxbaseamt().setScale(0, BigDecimal.ROUND_HALF_UP));
                   l.setTaxamt(l.getTaxamt().setScale(0, BigDecimal.ROUND_HALF_UP));
                   l.setTaxamt2(l.getTaxamt2().setScale(0, BigDecimal.ROUND_HALF_UP));
                   
                   list.add(l);
               }
           }
           catch (SQLException e)
           {
               s_log.log(Level.SEVERE, "product", e);
           }
           finally
           {
               DB.close(rs, pstmt);
               rs = null; pstmt = null;
           }    
           return list;
   } 

   
   private List<line> getInvoices30007(){
	
   List<line> list = new ArrayList<line>();

   PreparedStatement pstmt = null;
   ResultSet rs = null;
   
   String sql = 
//		   " SELECT  \n" +
//				   "row_number() over (order by invoice.c_invoice_id)  \n" +
////				   ",invoice.c_invoice_id\n" +
//				   ",bpartner.c_bpartner_id  \n" +
//				   ",bpartner.fi_tin  \n" +
//				   ",bpartner.fi_bin_iin  \n" +
//				   ",invoice.fi_invoiceno	 \n" +
//				   ",to_char(invoice.dateinvoiced,'dd.mm.yyyy') dateinvoiced  \n" +
//				   ",to_char(invoice.dateacct,'dd.mm.yyyy') dateacct  \n" +
//				   ",doctype.name  \n" +
//				   ",invoicetax.taxbaseamt taxbaseamt  \n" +
//				   ",invoicetax.taxamt taxamt  \n" +
//				   ",invoicetax.taxamt taxamt2  , ('квартал ' || case      				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 1 then 1      				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 2 then 1  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 3 then 1  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 4 then 2  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 5 then 2  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 6 then 2  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 7 then 3  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 8 then 3  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 9 then 3  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 10 then 4  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 11 then 4  	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 12 then 4  end  || ' год ' || cast(EXTRACT(YEAR from invoice.dateinvoiced) as int)) period  \n" +
//				   "FROM  adempiere.c_invoice invoice  \n" +
//				   "LEFT JOIN adempiere.c_invoicetax invoicetax on invoicetax.c_invoice_id = invoice.c_invoice_id  \n" +
//				   "LEFT JOIN adempiere.c_doctype doctype on doctype.c_doctype_id = invoice.c_doctype_id  \n" +
//				   "LEFT JOIN adempiere.c_bpartner bpartner on bpartner.c_bpartner_id = invoice.c_bpartner_id  \n" +
//				   "inner join adempiere.fact_acct fa on fa.record_id = invoice.c_invoice_id\n" +
//				   "inner join adempiere.c_elementvalue ce on fa.account_id = ce.c_elementvalue_id \n" +
//				   "WHERE doctype.c_doctype_id = 1000002  \n" +
//				   "and invoice.dateacct BETWEEN ? and ?  \n" +
//				   "and invoice.dateinvoiced BETWEEN ? and ?  \n" +
//				   "and invoice.docstatus = 'CO' \n" +
//				   "and invoicetax.taxamt > 0  \n" +
//				   "and fa.ad_table_id = 318\n" +
//				   "and fa.line_id is not null\n" +
//				   "and ce.value not like '721001'\n" +
//				   "order by invoice.c_invoice_id"		 ;  
		   	" SELECT "
			+ " row_number() over (order by invoice.c_invoice_id) "
			+ " ,bpartner.c_bpartner_id "
			+ " ,bpartner.fi_tin "
			+ " ,bpartner.fi_bin_iin "
			+ " ,invoice.documentno	"
			+ " ,to_char(invoice.dateinvoiced,'dd.mm.yyyy') dateinvoiced "
			+ " ,to_char(invoice.dateacct,'dd.mm.yyyy') dateacct "
			+ " ,doctype.name "
			+ " ,invoicetax.taxbaseamt taxbaseamt "
			+ " ,invoicetax.taxamt taxamt "
			+ " ,invoicetax.taxamt taxamt2 "	
			
//			+ " ,to_char(invoicetax.taxbaseamt,'9 999 999 999D99') taxbaseamt "
//			+ " ,to_char(invoicetax.taxamt,'999 999 999 999D99') taxamt "
//			+ " ,to_char(invoicetax.taxamt,'999 999 999 999D99') taxamt2 "	
			+ " , ('квартал ' || case "
			+ "     				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 1 then 1 "
			+ "     				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 2 then 1 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 3 then 1 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 4 then 2 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 5 then 2 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 6 then 2 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 7 then 3 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 8 then 3 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 9 then 3 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 10 then 4 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 11 then 4 "
			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 12 then 4 "
			+ " end "
			+ " || ' год ' || cast(EXTRACT(YEAR from invoice.dateinvoiced) as int)) period "
			+ " FROM "
			+ " c_invoice invoice "
			+ " LEFT JOIN c_invoicetax invoicetax on invoicetax.c_invoice_id = invoice.c_invoice_id "
			+ " LEFT JOIN c_doctype doctype on doctype.c_doctype_id = invoice.c_doctype_id "
			+ " LEFT JOIN c_bpartner bpartner on bpartner.c_bpartner_id = invoice.c_bpartner_id "
			+ " WHERE doctype.c_doctype_id = 1000002 "
			+ " and invoice.dateacct BETWEEN ? and ? "
			+ " and invoice.dateinvoiced BETWEEN ? and ? "
			+ " and invoice.docstatus = 'CO' " 
//			+ " and invoicetax.taxamt > 0"
			+ "	order by invoice.dateinvoiced"
			;

       try
       {
           pstmt = DB.prepareStatement(sql,null);

           pstmt.setTimestamp(1, startDate);
           pstmt.setTimestamp(2, endDate);
           pstmt.setTimestamp(3, startDate2);
           pstmt.setTimestamp(4, endDate2);
           
           rs = pstmt.executeQuery();    
           while (rs.next ())
           {
        	   line l = new line();
               l.setRow(String.valueOf(rs.getInt(1)));
               l.setC_bpartner_id(String.valueOf(rs.getInt(2)));
               l.setFi_tin(rs.getString(3));
               l.setFi_bin_iin(rs.getString(4));
               l.setDocumentno(rs.getString(5));
               l.setDateinvoiced(rs.getString(6));
               l.setDateacct(rs.getString(7));
               l.setName(rs.getString(8));
               l.setTaxbaseamt(rs.getBigDecimal(9));
               l.setTaxamt(rs.getBigDecimal(10));
               l.setTaxamt2(rs.getBigDecimal(11));
               l.setPeriod(rs.getString(12));
               
               l.setTaxbaseamt(l.getTaxbaseamt().setScale(0, BigDecimal.ROUND_HALF_UP));
               l.setTaxamt(l.getTaxamt().setScale(0, BigDecimal.ROUND_HALF_UP));
               l.setTaxamt2(l.getTaxamt2().setScale(0, BigDecimal.ROUND_HALF_UP));
               
               list.add(l);
           }
       }
       catch (SQLException e)
       {
           s_log.log(Level.SEVERE, "product", e);
       }
       finally
       {
           DB.close(rs, pstmt);
           rs = null; pstmt = null;
       }    
       return list;
} 
   
   
}